package cn.zw.msmp.common.helper;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import cn.zw.msmp.common.pojo.ExcelData;
import cn.zw.msmp.common.util.DateUtil;
import cn.zw.msmp.dao.TransactionDao;
import cn.zw.msmp.pojo.TransactionInfo;

@Component("excelImport")
public class ExcelImportHelper {

	private static final Logger log = LoggerFactory.getLogger(ExcelImportHelper.class);
	
	DecimalFormat df = new DecimalFormat("0");
	
	@Autowired
	private TransactionDao dao;
	
	public int importData(String path) {
		if(StringUtils.isNotEmpty(path)) {
			try {
				int datas = importExcel(path);
				return datas;
			} catch (Exception e) {
				e.printStackTrace();
				log.error("import excel error, path="+path, e);
			}
		}
		return 0;
	}

	private int importExcel(String filePath) throws Exception {
		InputStream ins = new FileInputStream(new File(filePath));
		int result = 0;
		XSSFWorkbook workbook = new XSSFWorkbook(ins);
		List<TransactionInfo> datas = new ArrayList<TransactionInfo>();
		XSSFSheet sheet1 = workbook.getSheetAt(0);//只读取第一个sheet数据
		if(null != sheet1) {
			for(int row = 1; row <= sheet1.getLastRowNum(); row++) {
				XSSFRow xssfRow = sheet1.getRow(row);
				if(null != xssfRow) {
					ExcelData data = new ExcelData();
					String wangwangName = getCellValue(xssfRow.getCell(0));
					if(StringUtils.isEmpty(wangwangName)) {
						break;
					}
					data.setWangwangName(wangwangName);
					data.setCustomerName(getCellValue(xssfRow.getCell(1)));
					data.setCustPhone(getCellValue(xssfRow.getCell(2)));
					data.setAddress(getCellValue(xssfRow.getCell(3)));
					data.setTransactionNum(getCellValue(xssfRow.getCell(4)));
					data.setTransactionTm(getCellValue(xssfRow.getCell(5)));
					data.setRefundTm(getCellValue(xssfRow.getCell(6)));
					data.setProdDesc(getCellValue(xssfRow.getCell(7)));
					data.setRefundReason(getCellValue(xssfRow.getCell(8)));
					data.setOrderDetail(getCellValue(xssfRow.getCell(9)));
					data.setProvince(getCellValue(xssfRow.getCell(10)));
					data.setCity(getCellValue(xssfRow.getCell(11)));
					data.setEvaluate(getCellValue(xssfRow.getCell(12)));
					data.setWxTransferDate(getCellValue(xssfRow.getCell(13)));
					data.setWxTransferAmount(getCellValue(xssfRow.getCell(14)));
					data.setReBuy(getCellValue(xssfRow.getCell(15)));
					data.setRecommend(getCellValue(xssfRow.getCell(16)));
					data.setRecommender(getCellValue(xssfRow.getCell(17)));
					data.setRemark(getCellValue(xssfRow.getCell(18)));					
					TransactionInfo info = transform(data);
					datas.add(info);
				}
			}
			result = dao.batchInsert(datas);
			log.info("finish import excel data");
		}
		return result;
	}
	
	private String getCellValue(XSSFCell cell) {
		if(null != cell) {
//			return cell.getStringCellValue();
			if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
				// 返回布尔类型的值
				return String.valueOf(cell.getBooleanCellValue());
			} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
				// 返回数值类型的值
				return String.valueOf(df.format(cell.getNumericCellValue()));
			} else if(cell.getCellType() == XSSFCell.CELL_TYPE_ERROR) {
				return "数据类型错误";
			} else {
				// 返回字符串类型的值
				return String.valueOf(cell.getStringCellValue());
			}
		}
		return null;
	}
	
	private TransactionInfo transform(ExcelData data) {
		TransactionInfo info = new TransactionInfo();
		info.setWangwangName(data.getWangwangName());
		info.setCustomerName(data.getCustomerName());
		info.setCustPhone(data.getCustPhone());
		info.setAddress(data.getAddress());
		info.setTransactionNum(StringUtils.isNotEmpty(data.getTransactionNum()) ? Integer.parseInt(data.getTransactionNum()) : 0);
		info.setProdDesc(data.getProdDesc());
		info.setRefundReason(data.getRefundReason());
		info.setOrderDetail(data.getOrderDetail());
		info.setProvince(data.getProvince());
		info.setCity(data.getCity());
		info.setEvaluate(data.getEvaluate());
		info.setWxTransferAmount(StringUtils.isNotEmpty(data.getTransactionNum()) ? Double.parseDouble(data.getTransactionNum()) : 0);
		info.setReBuy(StringUtils.isNotEmpty(data.getReBuy()) ? Integer.parseInt(data.getReBuy()) : 0);
		info.setRecommend(StringUtils.isNotEmpty(data.getRecommend()) ? Integer.parseInt(data.getRecommend()) : 0);
		info.setRecommender(data.getRecommender());
		info.setRemark(data.getRemark());
		try {
			info.setTransactionTm(StringUtils.isNotEmpty(data.getTransactionTm()) ? 
					DateUtil.convertString2Date(DateUtil.DEFAILT_DATE_PATTERN, data.getTransactionTm()) : null);
			info.setRefundTm(StringUtils.isNotEmpty(data.getRefundTm()) ? 
					DateUtil.convertString2Date(DateUtil.DEFAILT_DATE_PATTERN, data.getRefundTm()) : null);
			info.setWxTransferDate(StringUtils.isNotEmpty(data.getWxTransferDate()) ? 
					DateUtil.convertString2Date(DateUtil.DEFAILT_DATE_PATTERN, data.getWxTransferDate()) : null);
		} catch (Exception e) {
			log.error("tranform date error@import excel", e);
		}
		return info;
	}
}
